package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MLocator;
import org.compiere.model.MMovement;
import org.compiere.model.MMovementLine;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.utils.AnalogConvertor_RU;

import extend.org.compiere.utils.Util;

public class MM_Form_434_z extends SvrProcess {
	//Form_434_z_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_M_Movement_ID;
	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("M_Movement_ID")) 
				p_M_Movement_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	/* (non-Javadoc)
	 * @see org.compiere.process.SvrProcess#doIt()
	 */
	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "Form_434_z_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		sheet.getRow(8).getCell(0).setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(11).getCell(7).setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName());
		MMovement movement = new MMovement(m_ctx, p_M_Movement_ID, get_TrxName());
		
		String sql = "SELECT M_MovementLine_ID FROM M_MovementLine WHERE M_Movement_ID = "+p_M_Movement_ID;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int lineCount = 0;
		try{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 15;
			while(rs.next()){
				MMovementLine movementLine = new MMovementLine(m_ctx, rs.getInt(1), get_TrxName());
				row++; lineCount++;
				sheet.shiftRows(row, row+27, 1);
				R = sheet.getRow(row);
				R.setHeight(sheet.getRow(8).getHeight());
				for(int i=0; i < 43; i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(15).getCell(42).getCellStyle());
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5)); //col1
				R.getCell(0).setCellValue(movement.getDocumentNo());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 6, 11)); //col2
				R.getCell(6).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(movement.getMovementDate()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 17)); //col3
				sheet.addMergedRegion(new CellRangeAddress(row, row, 18, 23)); //col4
				R.getCell(18).setCellValue(new MLocator(m_ctx, movementLine.getM_LocatorTo_ID(), get_TrxName()).getValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 29)); //col5
				R.getCell(24).setCellValue(new MLocator(m_ctx, movementLine.getM_Locator_ID(), get_TrxName()).getValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 30, 35)); //col6
				sheet.addMergedRegion(new CellRangeAddress(row, row, 36, 42)); //col7
			}
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		//Second cycle of data
		BigDecimal totalMovementQty = new BigDecimal(0);
		BigDecimal totalSum = new BigDecimal(0);
		int tempLineCount = lineCount;
		try{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 18 + lineCount;
			while(rs.next()){
				MMovementLine movementLine = new MMovementLine(m_ctx, rs.getInt(1), get_TrxName());
				row++; tempLineCount++;
				sheet.shiftRows(row, row+21+tempLineCount, 1);
				R = sheet.getRow(row);
				R.setHeight(sheet.getRow(8).getHeight());
				for(int i=0; i < 43; i++){
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(15).getCell(42).getCellStyle());
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 11)); //col1
				R.getCell(0).setCellValue(movementLine.getM_Product().getName());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 17)); //col2
				R.getCell(12).setCellValue(movementLine.getM_Product().getC_UOM().getUOMSymbol());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 18, 23)); //col3
				R.getCell(18).setCellValue(movementLine.getM_Product().getValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 29)); //col4
				R.getCell(24).setCellValue(String.valueOf(movementLine.getMovementQty()));
				totalMovementQty = totalMovementQty.add(movementLine.getMovementQty());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 30, 35)); //col5
				//
				String sql1 = "SELECT SUM(AmtAcctDr) FROM Fact_Acct " 
						+ "WHERE  AD_Table_ID = 323 " 
						+ "AND Record_ID = "+p_M_Movement_ID 
						+ "AND DateAcct = '23/08/2012' " 
						+ "AND Line_ID = "+movementLine.getM_MovementLine_ID() 
						+ "GROUP BY Line_ID";
				PreparedStatement pstmt1 = null;
				ResultSet rs1 = null;
				try{
					pstmt1 = DB.prepareStatement(sql1.toString(), get_TrxName());
					rs1 = pstmt1.executeQuery();
					while(rs1.next()){
						R.getCell(30).setCellValue((rs1.getBigDecimal(1).divide(movementLine.getMovementQty())).toString());
						totalSum = totalSum.add(rs1.getBigDecimal(1));
						R.getCell(36).setCellValue(rs1.getBigDecimal(1).toString());
					}
				}
				catch(Exception e1){
					log.log(Level.SEVERE, sql1.toString(), e1);
				}
				finally{
					DB.close(rs1, pstmt1);
					rs1 = null; pstmt1 = null;
				}
				//
				sheet.addMergedRegion(new CellRangeAddress(row, row, 36, 42)); //col6
				
			}
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		AnalogConvertor_RU totalInWords = new AnalogConvertor_RU();
		sheet.getRow(21+tempLineCount).getCell(7).setCellValue(totalInWords.toString(totalMovementQty.intValue())+"("+String.valueOf(totalMovementQty)+")");
		sheet.getRow(21+tempLineCount).getCell(29).setCellValue(totalInWords.toString(totalSum.doubleValue()));	
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();		
		// Open the file with the application associated
					Util.launchFile(new File(file));
		return Msg.translate(m_ctx, "Success");
	}

}
